﻿#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PhotoGallery.Core.Business;
using CY.Utility.DBUtility;
using PhotoGallery.Infrastructure.Collections;
#endregion Using

namespace PhotoGallery.DALProviders.SqlServerProvider
{
    public class OperationProvider : PhotoGallery.Core.Providers.DALProvider.IOperationProvider
    {
        #region Sql
        private static readonly string SqlSelectOperation = "SELECT [Id], [Status],[AddDate],[Name],[Controller],[Action] FROM [Operation] WHERE  [Id]=@Id;";

        private static readonly string SqlInsertOperation = "INSERT INTO [Operation]([Status],[Name],[Controller],[Action]) values(@Status,@Name,@Controller,@Action);SELECT SCOPE_IDENTITY();";

        private static readonly string SqlUpdateOperation = "UPDATE [Operation] SET [Status] = @Status,[AddDate] = @AddDate,[Name] = @Name,[Controller] = @Controller,[Action] = @Action WHERE  [Id]=@Id;";

        private static readonly string SqlDeleteOperation = "DELETE [Operation] WHERE  [Id]=@Id;";

        private static readonly string SqlGetAllOperation = "SELECT [Id], [Status],[AddDate],[Name],[Controller],[Action] FROM [Operation];";

        private static readonly string SqlFind = "SELECT [Id], [Status],[AddDate],[Name],[Controller],[Action] FROM [Operation] where Controller=@Controller and Action=@Action";

        private static readonly string SqlSearch = "SELECT [Id], [Status],[AddDate],[Name],[Controller],[Action] FROM [Operation] where  ( Controller = @Controller or @Controller is null ) and ( Action = @Action or @Action is null ) and (Name =@Name or @Name is null) and (Status =@Status or @Status is null) ;";

        private static readonly string SqlSearchInner = "select  [Operation].[Id], [Operation].[Status],[Operation].[AddDate],[Operation].[Name],[Operation].[Controller],[Operation].[Action]  from role  inner join roleoperation  on role.id = @roleid and role.id=roleoperation.roleid and role.status = @status and roleoperation.status = @status  inner join operation  on roleoperation.operationid = operation.id  and operation.status = @status ";
        #endregion

        #region Procedures
        /*

-- Begin of Operation
IF OBJECT_ID(N'[USP_Operation_Delete_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_Operation_Delete_By_Id]

IF OBJECT_ID(N'[USP_Operation_Select_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_Operation_Select_By_Id]

IF OBJECT_ID(N'[USP_Operation_Insert]') IS NOT NULL
	DROP PROCEDURE [USP_Operation_Insert]

IF OBJECT_ID(N'[USP_Operation_Update_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_Operation_Update_By_Id]

IF OBJECT_ID(N'[USP_Operation_SelectAll]') IS NOT NULL
	DROP PROCEDURE [USP_Operation_SelectAll]

GO

--region [USP_Operation_Delete_By_Id]
CREATE PROCEDURE [USP_Operation_Delete_By_Id]
	@Id	int
AS
SET NOCOUNT ON
DELETE [Operation] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_Operation_Select_By_Id]
CREATE PROCEDURE [USP_Operation_Select_By_Id]
	@Id	int
AS
SET NOCOUNT ON
SELECT [Id], [Status],[AddDate],[Name],[Controller],[Action] FROM [Operation] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_Operation_Insert]
CREATE PROCEDURE [USP_Operation_Insert]
	@Status	int,
	@AddDate	datetime,
	@Name	nvarchar(50),
	@Controller	nvarchar(50),
	@Action	nvarchar(50)
AS
SET NOCOUNT ON
INSERT INTO [Operation]([Status],[AddDate],[Name],[Controller],[Action]) values(@Status,@AddDate,@Name,@Controller,@Action);SELECT SCOPE_IDENTITY();
--endregion
GO

--region [USP_Operation_Update_By_Id]
CREATE PROCEDURE [USP_Operation_Update_By_Id]
	@Status	int,
	@AddDate	datetime,
	@Name	nvarchar(50),
	@Controller	nvarchar(50),
	@Action	nvarchar(50),
	@Id	int
AS
SET NOCOUNT ON
UPDATE [Operation] SET [Status] = @Status,[AddDate] = @AddDate,[Name] = @Name,[Controller] = @Controller,[Action] = @Action WHERE  [Id]=@Id;
--endregion
GO

--region [USP_Operation_SelectAll]
CREATE PROCEDURE [USP_Operation_SelectAll]
AS
SET NOCOUNT ON
SELECT [Id], [Status],[AddDate],[Name],[Controller],[Action] FROM [Operation];
--endregion
GO


-- End of Operation
*/
        #endregion

        #region IOperationProvider Members
        public Core.Business.Operation Select(int id)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, id);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectOperation);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.Operation operation = new Core.Business.Operation();

                if (!reader.IsDBNull(0)) operation.Id = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) operation.Status = reader.GetInt32(1);
                if (!reader.IsDBNull(2)) operation.AddDate = reader.GetDateTime(2);
                if (!reader.IsDBNull(3)) operation.Name = reader.GetString(3);
                if (!reader.IsDBNull(4)) operation.Controller = reader.GetString(4);
                if (!reader.IsDBNull(5)) operation.Action = reader.GetString(5);

                reader.Close();
                return operation;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public void Insert(Core.Business.Operation operation)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Status", SqlDbType.Int, operation.Status);
            //sql.AddParameter("@AddDate", SqlDbType.DateTime, operation.AddDate);
            sql.AddParameter("@Name", SqlDbType.NVarChar, operation.Name);
            sql.AddParameter("@Controller", SqlDbType.NVarChar, operation.Controller);
            sql.AddParameter("@Action", SqlDbType.NVarChar, operation.Action);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertOperation);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                if (!reader.IsDBNull(0)) operation.Id = int.Parse(reader.GetDecimal(0).ToString());

                reader.Close();
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }
        }

        public void Update(Core.Business.Operation operation)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Status", SqlDbType.Int, operation.Status);
            sql.AddParameter("@AddDate", SqlDbType.DateTime, operation.AddDate);
            sql.AddParameter("@Name", SqlDbType.NVarChar, operation.Name);
            sql.AddParameter("@Controller", SqlDbType.NVarChar, operation.Controller);
            sql.AddParameter("@Action", SqlDbType.NVarChar, operation.Action);
            sql.AddParameter("@Id", SqlDbType.Int, operation.Id);
            sql.ExecuteSql(SqlUpdateOperation);
        }

        public void Delete(Core.Business.Operation operation)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, operation.Id);
            sql.ExecuteSql(SqlDeleteOperation);
        }

        public IList<Core.Business.Operation> GetAllOperation()
        {
            IList<Core.Business.Operation> operationlist = new List<Core.Business.Operation>();
            SqlServerUtility sql = new SqlServerUtility();

            SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllOperation);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.Operation operation = new Core.Business.Operation();

                    if (!reader.IsDBNull(0)) operation.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) operation.Status = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) operation.AddDate = reader.GetDateTime(2);
                    if (!reader.IsDBNull(3)) operation.Name = reader.GetString(3);
                    if (!reader.IsDBNull(4)) operation.Controller = reader.GetString(4);
                    if (!reader.IsDBNull(5)) operation.Action = reader.GetString(5);

                    operation.MarkOld();
                    operationlist.Add(operation);
                }
                reader.Close();
            }
            return operationlist;
        }
        #endregion


        public PagedList<Core.Business.Operation> Search(OperationSearch search)
        {
            IList<Core.Business.Operation> operationlist = new List<Core.Business.Operation>();
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Name", SqlDbType.NVarChar, !string.IsNullOrEmpty(search.Name) ? (object)search.Name : (object)DBNull.Value);
            sql.AddParameter("@Controller", SqlDbType.NVarChar, !string.IsNullOrEmpty(search.ControllerName) ? (object)search.ControllerName : (object)DBNull.Value);
            sql.AddParameter("@Action", SqlDbType.NVarChar, !string.IsNullOrEmpty(search.ActionName) ? (object)search.ActionName : (object)DBNull.Value);
            sql.AddParameter("@Status", SqlDbType.Int, search.Status != null ? (object)search.Status.Value : (object)DBNull.Value);

            SqlDataReader reader = sql.ExecuteSqlReader(SqlSearch);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.Operation operation = new Core.Business.Operation();

                    if (!reader.IsDBNull(0)) operation.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) operation.Status = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) operation.AddDate = reader.GetDateTime(2);
                    if (!reader.IsDBNull(3)) operation.Name = reader.GetString(3);
                    if (!reader.IsDBNull(4)) operation.Controller = reader.GetString(4);
                    if (!reader.IsDBNull(5)) operation.Action = reader.GetString(5);

                    operation.MarkOld();
                    operationlist.Add(operation);
                }
                reader.Close();
            }
            return new PagedList<Operation>(operationlist, 0, 100, 1000);
        }

        public Operation Find(string controller, string action)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Controller", SqlDbType.NVarChar, controller);
            sql.AddParameter("@Action", SqlDbType.NVarChar, action);

            SqlDataReader reader = sql.ExecuteSqlReader(SqlFind);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.Operation operation = new Core.Business.Operation();

                if (!reader.IsDBNull(0)) operation.Id = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) operation.Status = reader.GetInt32(1);
                if (!reader.IsDBNull(2)) operation.AddDate = reader.GetDateTime(2);
                if (!reader.IsDBNull(3)) operation.Name = reader.GetString(3);
                if (!reader.IsDBNull(4)) operation.Controller = reader.GetString(4);
                if (!reader.IsDBNull(5)) operation.Action = reader.GetString(5);

                reader.Close();
                return operation;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }


        public IList<Operation> Find(Role role, int? status)
        {
            IList<Core.Business.Operation> operationlist = new List<Core.Business.Operation>();

            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@roleid", SqlDbType.Int, role.Id);
            sql.AddParameter("@status", SqlDbType.Int, status.Value);

            SqlDataReader reader = sql.ExecuteSqlReader(SqlSearchInner);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.Operation operation = new Core.Business.Operation();

                    if (!reader.IsDBNull(0)) operation.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) operation.Status = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) operation.AddDate = reader.GetDateTime(2);
                    if (!reader.IsDBNull(3)) operation.Name = reader.GetString(3);
                    if (!reader.IsDBNull(4)) operation.Controller = reader.GetString(4);
                    if (!reader.IsDBNull(5)) operation.Action = reader.GetString(5);

                    operation.MarkOld();
                    operationlist.Add(operation);
                }
                reader.Close();
            }
            return operationlist;
        }
    }
}
